# -*- coding: utf-8 -*-
"""
股票移动平均线粘合分析
分析5日线、10日线、20日线的粘合情况
"""
import logging
import pandas as pd
from datetime import datetime
from database import DatabaseManager
from config import DATABASE_CONFIG

# 配置日志
# 获取项目根目录
PROJECT_ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
LOG_DIR = os.path.join(PROJECT_ROOT, 'logs')
os.makedirs(LOG_DIR, exist_ok=True)

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s [%(levelname)s] %(message)s',
    handlers=[
        logging.StreamHandler(),
        logging.FileHandler(os.path.join(LOG_DIR, 'ma_convergence_analysis.log'), encoding='utf-8')
    ]
)
logger = logging.getLogger(__name__)

class MAConvergenceAnalyzer:
    """移动平均线粘合分析器"""
    
    def __init__(self):
        self.db = DatabaseManager(DATABASE_CONFIG)
    
    def find_convergence_stocks(self, convergence_threshold: float = 0.05) -> pd.DataFrame:
        """
        查找均线粘合的股票
        
        Args:
            convergence_threshold: 粘合阈值，默认5%（0.05）
                                表示最高均线与最低均线的差值不超过最高均线的5%
        """
        sql = """
        WITH stock_ma AS (
            SELECT 
                sd.stock_code,
                sd.trade_date,
                sd.close_price,
                sd.volume,
                sd.change_pct,
                -- 计算5日移动平均线
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
                ) AS ma5,
                -- 计算10日移动平均线
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
                ) AS ma10,
                -- 计算20日移动平均线
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
                ) AS ma20,
                -- 为每个股票标记最新日期
                ROW_NUMBER() OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date DESC
                ) AS rn
            FROM new_stock_daily sd
            INNER JOIN new_stock_info si ON sd.stock_code = si.stock_code
            WHERE si.status = 'active'
        ),
        convergence_calc AS (
            SELECT 
                sm.stock_code,
                si.stock_name,
                si.exchange,
                si.industry,
                sm.close_price AS current_price,
                ROUND(sm.ma5, 2) AS ma5,
                ROUND(sm.ma10, 2) AS ma10,
                ROUND(sm.ma20, 2) AS ma20,
                sm.change_pct,
                sm.volume,
                sm.trade_date,
                -- 计算均线间的最大和最小值
                GREATEST(sm.ma5, sm.ma10, sm.ma20) AS max_ma,
                LEAST(sm.ma5, sm.ma10, sm.ma20) AS min_ma,
                -- 计算粘合度（最大均线与最小均线的差值百分比）
                ROUND((GREATEST(sm.ma5, sm.ma10, sm.ma20) - LEAST(sm.ma5, sm.ma10, sm.ma20)) / 
                      GREATEST(sm.ma5, sm.ma10, sm.ma20) * 100, 2) AS convergence_ratio,
                -- 计算价格相对于均线的位置
                ROUND((sm.close_price - sm.ma20) / sm.ma20 * 100, 2) AS price_vs_ma20_pct,
                -- 判断均线排列顺序
                CASE 
                    WHEN sm.ma5 > sm.ma10 AND sm.ma10 > sm.ma20 THEN '多头排列'
                    WHEN sm.ma5 < sm.ma10 AND sm.ma10 < sm.ma20 THEN '空头排列'
                    ELSE '震荡排列'
                END AS ma_arrangement
            FROM stock_ma sm
            INNER JOIN new_stock_info si ON sm.stock_code = si.stock_code
            WHERE sm.rn = 1  -- 只取最新日期
            AND sm.ma5 IS NOT NULL 
            AND sm.ma10 IS NOT NULL 
            AND sm.ma20 IS NOT NULL
        )
        SELECT *
        FROM convergence_calc
        WHERE convergence_ratio <= %s  -- 粘合阈值过滤
        ORDER BY convergence_ratio ASC, volume DESC
        """
        
        try:
            logger.info(f"开始查找均线粘合股票（粘合度<={convergence_threshold*100}%）...")
            results = self.db.execute_query(sql, (convergence_threshold * 100,))
            logger.info(f"SQL查询完成，找到 {len(results)} 只均线粘合股票")
            return pd.DataFrame(results)
        except Exception as e:
            logger.error(f"SQL查询失败: {e}")
            return pd.DataFrame()
    
    def find_convergence_stocks_with_volume_filter(self, convergence_threshold: float = 0.05, 
                                                 min_volume: int = 1000000) -> pd.DataFrame:
        """
        查找均线粘合的股票，并过滤成交量
        
        Args:
            convergence_threshold: 粘合阈值，默认5%
            min_volume: 最小成交量，默认100万股
        """
        sql = """
        WITH stock_ma AS (
            SELECT 
                sd.stock_code,
                sd.trade_date,
                sd.close_price,
                sd.volume,
                sd.change_pct,
                -- 计算5日移动平均线
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
                ) AS ma5,
                -- 计算10日移动平均线
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
                ) AS ma10,
                -- 计算20日移动平均线
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
                ) AS ma20,
                -- 为每个股票标记最新日期
                ROW_NUMBER() OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date DESC
                ) AS rn
            FROM new_stock_daily sd
            INNER JOIN new_stock_info si ON sd.stock_code = si.stock_code
            WHERE si.status = 'active'
        ),
        convergence_calc AS (
            SELECT 
                sm.stock_code,
                si.stock_name,
                si.exchange,
                si.industry,
                sm.close_price AS current_price,
                ROUND(sm.ma5, 2) AS ma5,
                ROUND(sm.ma10, 2) AS ma10,
                ROUND(sm.ma20, 2) AS ma20,
                sm.change_pct,
                sm.volume,
                sm.trade_date,
                -- 计算均线间的最大和最小值
                GREATEST(sm.ma5, sm.ma10, sm.ma20) AS max_ma,
                LEAST(sm.ma5, sm.ma10, sm.ma20) AS min_ma,
                -- 计算粘合度（最大均线与最小均线的差值百分比）
                ROUND((GREATEST(sm.ma5, sm.ma10, sm.ma20) - LEAST(sm.ma5, sm.ma10, sm.ma20)) / 
                      GREATEST(sm.ma5, sm.ma10, sm.ma20) * 100, 2) AS convergence_ratio,
                -- 计算价格相对于均线的位置
                ROUND((sm.close_price - sm.ma20) / sm.ma20 * 100, 2) AS price_vs_ma20_pct,
                -- 判断均线排列顺序
                CASE 
                    WHEN sm.ma5 > sm.ma10 AND sm.ma10 > sm.ma20 THEN '多头排列'
                    WHEN sm.ma5 < sm.ma10 AND sm.ma10 < sm.ma20 THEN '空头排列'
                    ELSE '震荡排列'
                END AS ma_arrangement
            FROM stock_ma sm
            INNER JOIN new_stock_info si ON sm.stock_code = si.stock_code
            WHERE sm.rn = 1  -- 只取最新日期
            AND sm.ma5 IS NOT NULL 
            AND sm.ma10 IS NOT NULL 
            AND sm.ma20 IS NOT NULL
        )
        SELECT *
        FROM convergence_calc
        WHERE convergence_ratio <= %s  -- 粘合阈值过滤
        AND volume >= %s  -- 成交量过滤
        ORDER BY convergence_ratio ASC, volume DESC
        """
        
        try:
            logger.info(f"开始查找均线粘合股票（粘合度<={convergence_threshold*100}%，成交量>={min_volume:,}）...")
            results = self.db.execute_query(sql, (convergence_threshold * 100, min_volume))
            logger.info(f"SQL查询完成，找到 {len(results)} 只均线粘合股票")
            return pd.DataFrame(results)
        except Exception as e:
            logger.error(f"SQL查询失败: {e}")
            return pd.DataFrame()
    
    def get_convergence_statistics(self, df: pd.DataFrame) -> dict:
        """获取粘合分析统计信息"""
        if df.empty:
            return {}
        
        stats = {
            'total_stocks': len(df),
            'avg_convergence_ratio': round(df['convergence_ratio'].mean(), 2),
            'min_convergence_ratio': round(df['convergence_ratio'].min(), 2),
            'max_convergence_ratio': round(df['convergence_ratio'].max(), 2),
            'avg_volume': int(df['volume'].mean()),
            'total_volume': int(df['volume'].sum()),
        }
        
        # 按粘合度分组统计
        convergence_ranges = [
            (0, 1, "极强粘合(0-1%)"),
            (1, 2, "强粘合(1-2%)"),
            (2, 3, "中等粘合(2-3%)"),
            (3, 5, "弱粘合(3-5%)")
        ]
        
        convergence_distribution = {}
        for min_ratio, max_ratio, label in convergence_ranges:
            count = len(df[(df['convergence_ratio'] >= min_ratio) & (df['convergence_ratio'] < max_ratio)])
            convergence_distribution[label] = count
        
        stats['convergence_distribution'] = convergence_distribution
        
        # 按均线排列统计
        if 'ma_arrangement' in df.columns:
            arrangement_stats = df['ma_arrangement'].value_counts().to_dict()
            stats['arrangement_distribution'] = arrangement_stats
        
        # 按行业统计
        if 'industry' in df.columns:
            industry_stats = df['industry'].value_counts().head(10).to_dict()
            stats['top_industries'] = industry_stats
        
        return stats
    
    def save_results(self, df: pd.DataFrame, filename: str = None):
        """保存分析结果"""
        if df.empty:
            logger.warning("没有均线粘合股票数据可保存")
            return
        
        if filename is None:
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            filename = f'ma_convergence_stocks_{timestamp}.csv'
        
        try:
            df.to_csv(filename, index=False, encoding='utf-8-sig')
            logger.info(f"分析结果已保存到: {filename}")
        except Exception as e:
            logger.error(f"保存结果失败: {e}")
    
    def print_results(self, df: pd.DataFrame):
        """打印分析结果"""
        if df.empty:
            print("未找到均线粘合的股票")
            return
        
        print(f"\n=== 均线粘合股票分析结果 ===")
        print(f"共找到 {len(df)} 只均线粘合股票")
        print(f"分析时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
        print("\n股票列表:")
        print("-" * 140)
        print(f"{'代码':<8} {'名称':<12} {'交易所':<6} {'行业':<20} {'当前价':<8} {'5日线':<8} {'10日线':<8} {'20日线':<8} {'粘合度':<8} {'排列':<8} {'涨跌幅':<8} {'成交量':<12} {'日期':<12}")
        print("-" * 140)
        
        for _, row in df.iterrows():
            print(f"{row['stock_code']:<8} {row['stock_name']:<12} {row['exchange']:<6} "
                  f"{str(row['industry'])[:18]:<20} {row['current_price']:<8.2f} "
                  f"{row['ma5']:<8.2f} {row['ma10']:<8.2f} {row['ma20']:<8.2f} "
                  f"{row['convergence_ratio']:<8.2f}% {row['ma_arrangement']:<8} "
                  f"{row['change_pct']:<8.2f} {row['volume']:<12} {row['trade_date']:<12}")
        
        print("-" * 140)
        
        # 打印统计信息
        stats = self.get_convergence_statistics(df)
        if stats:
            print(f"\n=== 统计信息 ===")
            print(f"平均粘合度: {stats['avg_convergence_ratio']}%")
            print(f"最小粘合度: {stats['min_convergence_ratio']}%")
            print(f"最大粘合度: {stats['max_convergence_ratio']}%")
            print(f"平均成交量: {stats['avg_volume']:,}")
            print(f"总成交量: {stats['total_volume']:,}")
            
            # 粘合度分布
            if 'convergence_distribution' in stats:
                print(f"\n粘合度分布:")
                for label, count in stats['convergence_distribution'].items():
                    print(f"  {label}: {count} 只")
            
            # 均线排列分布
            if 'arrangement_distribution' in stats:
                print(f"\n均线排列分布:")
                for arrangement, count in stats['arrangement_distribution'].items():
                    print(f"  {arrangement}: {count} 只")
            
            # 行业分布
            if 'top_industries' in stats:
                print(f"\n行业分布（前10名）:")
                for industry, count in stats['top_industries'].items():
                    print(f"  {industry}: {count} 只")

def main():
    """主函数"""
    try:
        analyzer = MAConvergenceAnalyzer()
        
        # 分析所有均线粘合股票（粘合度<=5%）
        print("=== 分析均线粘合股票（粘合度<=5%） ===")
        convergence_df = analyzer.find_convergence_stocks(0.05)
        analyzer.print_results(convergence_df)
        analyzer.save_results(convergence_df, 'ma_convergence_all.csv')
        
        print("\n" + "="*60)
        
        # 分析均线粘合股票（粘合度<=3%，成交量>100万）
        print("=== 分析均线粘合股票（粘合度<=3%，成交量>100万） ===")
        convergence_df_filtered = analyzer.find_convergence_stocks_with_volume_filter(0.03, 1000000)
        analyzer.print_results(convergence_df_filtered)
        analyzer.save_results(convergence_df_filtered, 'ma_convergence_filtered.csv')
        
        print("\n" + "="*60)
        
        # 分析极强粘合股票（粘合度<=1%）
        print("=== 分析极强粘合股票（粘合度<=1%） ===")
        strong_convergence_df = analyzer.find_convergence_stocks(0.01)
        analyzer.print_results(strong_convergence_df)
        analyzer.save_results(strong_convergence_df, 'ma_convergence_strong.csv')
        
    except Exception as e:
        logger.error(f"分析过程出错: {e}")
        raise

if __name__ == "__main__":
    main()
